<?php

namespace App\Models\Ocean\Report;

use Illuminate\Support\Facades\DB;

class OceanPromotionReportModel extends OceanReportV2Model
{
    /**
     * 关联到模型的数据表
     *
     * @var string
     */
    protected $table = 'ocean_promotion_reports';

    /**
     * 关联到模型数据表的主键
     *
     * @var string
     */
    protected $primaryKey = 'id';

    /**
     * @param $query
     * @return mixed
     * 计算
     */
    public function scopeCompute($query){
        return $query->select(DB::raw("
            SUM(`stat_cost`) `stat_cost`,
            SUM(`click_cnt`) `click_cnt`,
            SUM(`show_cnt`) `show_cnt`,
            SUM(`convert_cnt`) `convert_cnt`,
            ROUND(SUM(`stat_cost` / 100) / SUM(`show_cnt`) * 1000, 2) `show_cost`,
            ROUND(SUM(`stat_cost` / 100) / SUM(`click_cnt`), 2) `click_cost`,
            CONCAT(ROUND(SUM(`click_cnt`) / SUM(`show_cnt`) * 100, 2), '%') `click_rate`,
            ROUND(SUM(`stat_cost` / 100) / SUM(`convert_cnt`), 2) `convert_cost`,
            CONCAT(ROUND(SUM(`convert_cnt`) / SUM(`click_cnt`) * 100, 2), '%') `convert_rate`
        "));
    }
}
